package com.skywolf.chem.common.excel;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import sun.reflect.generics.reflectiveObjects.NotImplementedException;

import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;


public class ExcelReader {


    public static ExcelTableIt read(ExcelTable.ExcelType excelType, InputStream is) throws Exception{
        Workbook wb;

        if (ExcelTable.ExcelType.XLS.equals(excelType)) {
            POIFSFileSystem ps=new POIFSFileSystem(is);
            wb = new HSSFWorkbook(ps);
        } else {
            wb = new XSSFWorkbook(is) ;
        }
        Sheet sheet = wb.getSheetAt(0);
        Row row = sheet.getRow(0);
        // 标题总列数
        int colNum = row.getPhysicalNumberOfCells();

        String[] title = new String[colNum];
        for (int i = 0; i < colNum; i++) {
            title[i] = getStringCellValue(row.getCell((short) i));
        }
//        int rowNum=sheet.getLastRowNum();
        //List<Map<String,String>> result=new ArrayList<>();

//        for(int i=1;i<=rowNum;i++){ //从1开始
//            Map<String,String> rowMap=new LinkedHashMap<>();//有序
//            if(isEmpty(sheet.getRow(i),colNum))
//                break;//取到空行，跳出不再取值
//            for(int j=0;j<colNum;j++) {
//                Cell cell = getMergedCell(sheet, i, j);
//                if (cell==null) {
//                    cell = sheet.getRow(i).getCell((short) j);
//                }
//                rowMap.put(title[j], getStringCellValue(cell));
//            }
//            result.add(rowMap);
//        }
        if(title==null || title.length==0){
            throw new  IllegalArgumentException("传入的文件header为空.");
        }
        return new ExcelTableIt(Arrays.asList(title),new ExcelIterator(sheet,title));
    }

    private  static  String getStringCellValue(Cell cell) {
        if(cell==null)
            return "";
        String strCell = "";
        switch (cell.getCellType()) {
            case STRING:
                strCell = cell.getStringCellValue();
                break;
            case NUMERIC:
                double value=Double.valueOf( cell.getNumericCellValue());
                DecimalFormat df = new DecimalFormat("###0.0###");//最多保留几位小数，就用几个#，最少位就用0来确定
                strCell = df.format(value);
                break;
            case BOOLEAN:
                strCell = String.valueOf(cell.getBooleanCellValue());
                break;
            case BLANK:
                strCell = "";
                break;
            default:
                strCell = "";
                break;
        }
        if (strCell.equals("") || strCell == null) {
            return "";
        }
        if (cell == null) {
            return "";
        }
        return strCell;
    }
    private static  boolean isEmpty(Row row, int column){
        if(row==null)
            return  true;
        boolean ret=true;
        for(int i=0;i<column;i++){
            Cell cell=   row.getCell(i);

            if(cell!=null && !StringUtils.isEmpty(getStringCellValue(cell))){
                ret=false;
                break;
            }
        }
        return ret;
    }
    private static Cell getMergedCell(Sheet sheet, int row , int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();

        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if(row>=firstRow && row<=lastRow && column>=firstColumn && column<=lastColumn){
                return  sheet.getRow(firstRow).getCell(firstColumn);
            }
        }
        return null;
    }

    private static class ExcelIterator  implements Iterator<ExcelRow> {

        Sheet sheet;
        int rowNum;
        String[] headers;
        int colNum;
        int pos;
        Map<String, String> rowMap;
        public ExcelIterator(Sheet sheet, String[] headers) {
            this.sheet=sheet;
            this.headers=headers;
            rowNum=sheet.getLastRowNum();
            colNum=this.headers.length;
            pos=1;
        }


        @Override
        public boolean hasNext() {

            while (pos<=rowNum){
                if(isEmpty(sheet.getRow(pos),colNum)) {
                    rowMap=null;
                    return false;
                }
                else{
                    rowMap = new HashMap<>();
                    for(int j=0;j<colNum;j++) {
                        Cell cell = getMergedCell(sheet, pos, j);
                        if (cell==null) {
                            cell = sheet.getRow(pos).getCell((short) j);
                        }
                        rowMap.put(headers[j], getStringCellValue(cell));
                    }
                    pos++;
                    return true;
                }

            }
            return false;
        }

        @Override
        public ExcelRow next() {
            return new ExcelRow(rowMap);
        }
        @Override
        public void remove() {
            throw  new NotImplementedException();
        }
    }
}
